<?php
// SQL文の組み立て時に、代入する文字列はmysql_real_escape_stringなどで必ずエスケープするべき
//あらかじめ許可されたパラメータのみを通すようにコーディングしたたほうが良い

require_once("MDB2.php");

function sanitizeTag($var)
{
    if ( is_array($var) )
    {
        $var = array_map("sanitizeTag", $var);
    }
    else
    {
        $var = htmlspecialchars(trim($var), ENT_QUOTES, 'UTF-8');

    }
    return $var;
}

function dump($text)
{
    $weekday = array("Sun","Mon","Tue","Wed","Thu","Fri","Sat");
    $filename = 'dump.txt';
    $systemDate = getDate();
    ob_start();
    var_dump( $text );
    $out = '[' . date('Y/n/j(') . $weekday[date('w')] . date(')G:i:s] : ') . ob_get_contents();
    ob_end_clean();
    file_put_contents( $filename, $out, FILE_APPEND );
}

$postdata = sanitizeTag($_POST);

$postdata['page'] = ( !$postdata['page'] ) ? 1 : $postdata['page'];
$postdata['rp'] = ( !$postdata['rp'] ) ? 10 : $postdata['rp'];
$postdata['sortname'] = ( !$postdata['sortname'] ) ? 'check_history_id' : $postdata['sortname'];
$postdata['sortorder'] = ( !$postdata['sortorder'] ) ? 'asc' : $postdata['sortorder'];

dump($postdata);

try
{
    // DB接続
    $dsn = "mysqli://root:juliana@localhost/olio";
    $options = array();
    $mdb2 =& MDB2::connect($dsn, $options);
    if (PEAR::isError($mdb2)) {
        die($mdb2->getMessage());
    }

    // フェッチモードを設定
    $mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);

    $from = ' from m_user mu, m_check_item mci, m_check_result mcr, t_check_history tch';

    $where = ' where tch.user_id = mu.user_id';
    $where.= '   and tch.check_item_id = mci.check_item_id';
    $where.= '   and tch.check_result_id = mcr.check_result_id';
    $where = $where . (($postdata['qtype'] && $postdata['query']) ? ' and ' . $postdata['qtype'] . ' like \'%'. $postdata['query']. '%\'' : '');

dump('select COUNT(*)' . $from . $where);

    // 件数取得
    $stmt =& $mdb2->query( 'select COUNT(*)' . $from . $where );
    if (PEAR::isError($stmt)) {
        die($stmt->getMessage());
    }
dump('①');

    $count = $stmt->fetchOne();
dump('②');

    // DBデータ取得
    $sql = "select tch.check_history_id, tch.created_at, mu.user_name, mu.user_name_kana, mci.check_item_name, mcr.check_result_name";
    $sql.= $from;
    $sql.= $where .
        ' order by ' . $postdata['sortname'] . ' ' . $postdata['sortorder'] . 
        ' limit ' . ( ( $postdata['page'] - 1 ) * $postdata['rp'] ) . ', ' . $postdata['rp']; 

dump($sql);

    $stmt =& $mdb2->query( $sql );
    if (PEAR::isError($stmt)) {
        die($stmt->getMessage());
    }



    $json = array
    (
        'page' => $postdata['page'] - 0,
        'total' => $count,
    );

    // DBデータを1レコードずつ JSON化
    while( $row = $stmt->fetchRow() )
    {
        foreach($row as $key => $value)
        {
            $tmp[] = $value;
        }

        $json['rows'][] = 
        (
            array
            (
                'cell' => $tmp
            )
        );

        unset($tmp,$key,$value);
    }

    $stmt = null;

    echo json_encode($json);

dump($json);


}
catch (PDOException $e)
{
    dump($e);
}

// 切断
$mdb2->disconnect();

